SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [xz_Mailings](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[MailingId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_xz_Mailings_MailingId]  DEFAULT (newid()),
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Subject] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PlainTextBody] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[HtmlBody] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_xz_Mailings] PRIMARY KEY CLUSTERED 
(
	[MailingId] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [xz_Mailings]  WITH CHECK ADD  CONSTRAINT [FK_xz_Mailings_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [aspnet_Applications] ([ApplicationId])

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [xz_Mailings_DeleteMailing]
(
    @ApplicationName	nvarchar(256),
	@MailingId			uniqueidentifier
)
AS
BEGIN
    SELECT  @MailingId = m.MailingId
    FROM    [xz_Mailings] m, aspnet_Applications a
    WHERE   m.MailingId = @MailingId
        AND m.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@MailingId IS NULL)
        RETURN 0

	DELETE xz_Mailings WHERE MailingId = @MailingId
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [xz_Mailings_GetMailingByID]
(
    @ApplicationName	nvarchar(256),
    @MailingId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT MailingID, AddedDate, AddedBy, Subject, PlainTextBody, HtmlBody
		FROM xz_Mailings
		WHERE MailingId = @MailingId
			AND ApplicationId = @ApplicationId
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [xz_Mailings_GetMailings]
(
    @ApplicationName	nvarchar(256),
	@ToDate				datetime
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT MailingId, AddedDate, AddedBy, Subject
	   FROM xz_Mailings
	   WHERE AddedDate <= @ToDate AND ApplicationId = @ApplicationId
	   ORDER BY AddedDate DESC

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [xz_Mailings_InsertMailing]
(
    @ApplicationName	nvarchar(256),
	@AddedDate			datetime,
	@AddedBy			nvarchar(256),
	@Subject			nvarchar(256),
	@PlainTextBody		ntext,
	@HtmlBody			ntext,
	@MailingId			uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

    IF( @MailingId IS NULL )
        SELECT @MailingId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT MailingId FROM xz_Mailings
                    WHERE MailingId = @MailingId ) )
            RETURN -1
    END
   
	INSERT INTO xz_Mailings
	   (ApplicationId, MailingId, AddedDate, AddedBy, Subject, PlainTextBody, HtmlBody)
	   VALUES (@ApplicationId, @MailingId, @AddedDate, @AddedBy, @Subject, @PlainTextBody, @HtmlBody)

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [xz_Mailings_UpdateMailing]
(
    @ApplicationName	nvarchar(256),
	@MailingID			uniqueidentifier,
	@Subject			nvarchar(256),
	@PlainTextBody		ntext,
	@HtmlBody			ntext
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_Mailings
	   SET Subject = @Subject, PlainTextBody = @PlainTextBody, HtmlBody = @HtmlBody
	   WHERE MailingId = @MailingId	

END